﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.IO;

public partial class ws_Catalog_CheckPoints : System.Web.UI.Page
{

    string ConnStr = GetAppSetting("1CConnect");
    string strYear, strMonth;

    protected override void OnPreInit(EventArgs e)
    {
        // Enabled trace in code
        this.Context.Trace.IsEnabled = false;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.Params["Year"] != null)
            strYear = Request.Params["Year"].ToString();
        else
            strYear = DateTime.Now.Year.ToString();

        if (Request.Params["Month"] != null)
            strMonth = Request.Params["Month"].ToString();
        else
            strMonth = DateTime.Now.Month.ToString();

        ConnStr = ConnStr.Replace("Zar_kor_81", "RetailStat");
        XmlDocument xReturn = getXML();
        Response.ContentType = "text/xml";
        Response.Write(xReturn.OuterXml);
    }

    public XmlDocument getXML()
    {
        int Year = Convert.ToInt32(strYear);
        int Month = Convert.ToInt32(strMonth);

        XmlDocument xReturn = new XmlDocument();

        DataTable tbl = new DataTable("tbl");
        Exception err = GetTbl(ref tbl, ConnStr);

        if (err == null)
        {
            tbl.Columns[0].ColumnName = "ID";
            tbl.Columns[0].Caption = "ID";
            tbl.Columns[1].ColumnName = "Name";
            tbl.Columns[1].Caption = "Name";
        }
        else
        {
            tbl.Columns.Add("ID", typeof(string));
            tbl.Columns[0].Caption = "ID";
            tbl.Columns.Add("Name", typeof(string));
            tbl.Columns[1].Caption = "Name";

            DataRow r = tbl.NewRow();
            r[0] = "-1000";
            r[1] = "Ошибка получения данных.";
            tbl.Rows.Add(r);
            return xReturn;
        }

        using (StringWriter stringWriter = new StringWriter())
        {
            using (XmlTextWriter xmlTextwriter = new XmlTextWriter(stringWriter))
            {
                tbl.WriteXml(xmlTextwriter, XmlWriteMode.WriteSchema);
                //XmlNode xRoot = xReturn.CreateElement("root");
                //xReturn.AppendChild(xRoot);
                xReturn.LoadXml(stringWriter.ToString());

                foreach (XmlNode node0 in xReturn.ChildNodes)
                {
                    foreach (XmlNode node1 in node0.ChildNodes)
                    {
                        if (node1.Name == "tbl")
                        {
                            foreach (XmlNode node2 in node1.ChildNodes)
                            {
                                if (node2.Name == "Name")
                                {
                                    string shopnameTmp = node2.InnerText;
                                    DataTable tblData = new DataTable("row");
                                    err = GetCheckPointsData(ref tblData, shopnameTmp, Year, Month);
                                    if (err == null)
                                    {
                                        using (StringWriter stringWriter2 = new StringWriter())
                                        {
                                            using (XmlTextWriter xmlTextwriter2 = new XmlTextWriter(stringWriter2))
                                            {
                                                tblData.WriteXml(xmlTextwriter2, XmlWriteMode.IgnoreSchema);
                                                XmlDocument xTmp = new XmlDocument();
                                                xTmp.LoadXml(stringWriter2.ToString());
                                                foreach (XmlNode xxx in xTmp.ChildNodes[0].ChildNodes)
                                                {
                                                    XmlNode rowNode = xReturn.CreateElement("row");
                                                    XmlNode rowItemDay = xReturn.CreateElement("Day");
                                                    rowItemDay.InnerText = xxx.ChildNodes[0].InnerText;
                                                    rowNode.AppendChild(rowItemDay);
                                                    XmlNode rowItemRealIn = xReturn.CreateElement("RealIn");
                                                    rowItemRealIn.InnerText = xxx.ChildNodes[1].InnerText;
                                                    rowNode.AppendChild(rowItemRealIn);
                                                    node1.AppendChild(rowNode);
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

            }
        }

        return xReturn;
    }

    Exception GetTbl(ref DataTable tbl, string parConnStr)
    {
        Exception ret = null;
        string connStr = parConnStr;
        connStr = connStr.Replace("Zar_kor_81", "RetailStat");
        string sql1 =
    @"SELECT ID, Name FROM Shops ORDER BY Name";
        try
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql1, conn))
                {
                    using (OleDbDataAdapter a = new OleDbDataAdapter(cmd))
                    {
                        if (tbl == null)
                            tbl = new DataTable();
                        a.Fill(tbl);
                    }
                }
            }
        }
        catch (Exception err)
        {
            ret = new Exception(err.Message, err);
        }
        return ret;
    }

    Exception GetCheckPointsData(ref DataTable tbl, string Point, int Year, int Month)
    {
        Exception ret = null;
        string connStr = GetAppSetting("1CConnect");
        connStr = connStr.Replace("Zar_kor_81", "RetailStat");
        string sql1 =
"SELECT DAY(t.DateCreated) as Day,SUM(t.RealIn) as RealIn FROM Shops s "
+ "INNER JOIN Entries e ON e.ShopID=s.ID INNER JOIN Sensors n ON n.EntryID=e.ID "
+ "INNER JOIN Traffic t ON t.SensorID=n.ID WHERE "
+ "YEAR(t.DateCreated)=" + Year.ToString() + " AND MONTH(t.DateCreated)=" + Month.ToString()
+ " AND s.Name='" + Point + "'"
+ " GROUP BY s.[Name], DAY(t.DateCreated) ";

        try
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql1, conn))
                {
                    using (OleDbDataAdapter a = new OleDbDataAdapter(cmd))
                    {
                        if (tbl == null)
                            tbl = new DataTable();
                        a.Fill(tbl);
                    }
                }
            }
        }
        catch (Exception err)
        {
            ret = new Exception(err.Message, err);
        }
        return ret;
    }

    static public string GetAppSetting(String mName)
    {
        String vStr = null;
        string strPath =
    @"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(strPath);
        XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
        foreach (XmlNode keynode in node.ChildNodes)
        {
            string keyStr = keynode.Attributes["key"].Value;
            if (keyStr == mName)
            {
                vStr = keynode.Attributes["value"].Value;
                break;
            }
        }

        if (String.IsNullOrEmpty(vStr))
            vStr = "";

        return vStr;
    }

}
